import datetime
from typing import Optional, Annotated, Final, List

from sqlalchemy import ForeignKey, Integer, Table, Column, String
from sqlalchemy.ext.associationproxy import AssociationProxy, association_proxy
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.orm import DeclarativeBase

str_150 = Annotated[str, 150]
str_50 = Annotated[str, 50]
int_pk = Annotated[int, mapped_column(primary_key=True)]


class Base(DeclarativeBase):
    ...


class Authors(Base):
    __tablename__ = 'authors'

    id: Mapped[int_pk]
    first_name: Mapped[str_50]
    last_name: Mapped[str_50]


class Books(Base):
    __tablename__ = 'books'

    id: Mapped[int_pk]
    title: Mapped[str_150]
    count: Mapped[int] = mapped_column(default=1)
    release_date: Mapped[datetime.date]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id", ondelete="CASCADE"))

    def to_json(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Students(Base):
    __tablename__ = 'students'

    id: Mapped[int_pk]
    first_name: Mapped[str_50]
    last_name: Mapped[str_50]
    phone: Mapped[int]
    email: Mapped[str_150]

    # association proxy
    student_book_associations: Mapped[List["ReceivingBooks"]] = relationship(
        back_populates="students",
        cascade="all, delete-orphan",
    )
    books: AssociationProxy[List[Books]] = association_proxy(
        "student_book_associations",
        "book",
        creator=lambda book_obj: ReceivingBooks(keyword=book_obj),
    )


class ReceivingBooks(Base):
    __tablename__ = "receiving_books"
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"), primary_key=True)
    student_id: Mapped[int] = mapped_column(ForeignKey("students.id"), primary_key=True)
    book: Mapped[Books] = relationship(back_populates="user_keyword_associations")
    # keyword: Mapped[Keyword] = relationship()


